import pandas as pd
import os
import glob
from openpyxl import Workbook
import flet as fl
import timeit
def main(page: fl.Page):
    #page.title = "Cpk Tool"
    page.padding=0
    info=fl.Text(value="Ready",size=20,color=fl.colors.YELLOW_700,text_align=fl.TextAlign.CENTER)
    page.window.width=300
    page.window.height=260
    def re_fresh(e):
        info.value="Ready"
        info.color=fl.colors.YELLOW_700
        page.update()
    page.appbar=fl.AppBar(title=fl.Text("Cpk Tool",color=fl.colors.BLUE_GREY_900,text_align=fl.alignment.center),
                        actions=[fl.IconButton(icon=fl.icons.REFRESH,on_click=re_fresh)])
    def sum_export(e):
        info.value="Running..."
        info.color=fl.colors.YELLOW_700
        page.update()
        folder_path = 'd:/data'
        output_file = 'D:/Cpk Summary.xlsx'
        final = []
        excel_files =glob.glob(os.path.join(folder_path, '*.xlsx'))
        for excel_file in excel_files:
            with pd.ExcelFile(excel_file) as xlsx:
                sheet=xlsx.sheet_names
                for sht in sheet:
                    if sht[:10]=="Data Input":
                        df=pd.read_excel(excel_file,sht)
                        name=df.iloc[4].tolist()
                        description=df.iloc[5].tolist()
                        for i in range(len(name)):
                            final.append([name[i],description[i]])
                        break
                for sht in sheet:
                    if sht[:10]=="Data Input":
                        df=pd.read_excel(excel_file,sht)
                        nor=df.iloc[12].tolist()
                        tola=df.iloc[13].tolist()
                        tolb=df.iloc[14].tolist()
                        stdv=df.iloc[19].tolist()
                        mean=df.iloc[20].tolist()
                        cp=df.iloc[23].tolist()
                        cpk=df.iloc[26].tolist()
                        yie=df.iloc[28].tolist()
                        for i in range(len(nor)):
                            final[i].append(nor[i])
                            final[i].append(tola[i])
                            final[i].append(tolb[i])
                            final[i].append(stdv[i])
                            final[i].append(mean[i])
                            final[i].append(cp[i])
                            final[i].append(cpk[i])
                            final[i].append(yie[i])
                            final[i].append("")
        wb=Workbook()
        ws=wb.active
        ws.title="data"
        for s in range(14,len(final)):
            ws.append(final[s])
        wb.save(output_file)
        info.value="Done"
        info.color=fl.colors.GREEN
        page.update()
    def export(e):
        a=timeit.default_timer()
        info.value="Running..."
        info.color=fl.colors.YELLOW_700
        page.update()
        folder_path = 'd:/data'
        output_file = 'D:/JMP.xlsx'
        start_row = 39
        end_row = 71
        final = []
        excel_files =glob.glob(os.path.join(folder_path, '*.xlsx'))
        for excel_file in excel_files:
            with pd.ExcelFile(excel_file) as xlsx:
                sheet=xlsx.sheet_names
                for sht in sheet:
                    if sht[:10]=="Data Input":
                        tool=sht[11:]
                        df=pd.read_excel(excel_file,sht)
                        for i in range(start_row, end_row):
                            local=[tool]
                            row=df.iloc[i].to_list()
                            for j in range(15,len(row)):
                                if isinstance(row[j], (float, int)) and pd.notnull(row[j]):
                                    local.append(row[j])
                            final.append(local)
        wb=Workbook()
        ws=wb.active
        ws.title="data"
        ws.append(['TOOL','spc'])
        for f in final:
            ws.append(f)
        wb.save(output_file)
        b=timeit.default_timer()
        print(b-a)
        info.value="Done"
        info.color=fl.colors.GREEN
        page.update()
    def row_export(e):
        info.value="Running..."
        info.color=fl.colors.YELLOW_700
        page.update()
        folder_path = 'd:/data'
        output_file = 'D:/JMP.xlsx'
        start_row = 39
        end_row = 71
        final = []
        excel_files =glob.glob(os.path.join(folder_path, '*.xlsx'))
        for excel_file in excel_files:
            with pd.ExcelFile(excel_file) as xlsx:
                sheet=xlsx.sheet_names
                for sht in sheet:
                    if sht[:8]=="Raw data":
                        tool=sht[9:]
                        df=pd.read_excel(excel_file,sht)
                        for i in range(start_row, end_row):
                            local=[tool]
                            row=df.iloc[i].to_list()
                            for j in range(15,len(row)):
                                if isinstance(row[j], (float, int)) and pd.notnull(row[j]):
                                    local.append(row[j])
                            final.append(local)
        wb=Workbook()
        ws=wb.active
        ws.title="data"
        ws.append(['TOOL','spc'])
        for f in final:
            ws.append(f)
        wb.save(output_file)
        info.value="Done"
        info.color=fl.colors.GREEN
        page.update()
    page.add(
        fl.Column([
            fl.ElevatedButton(
                "Cpk Data",
                icon=fl.icons.COPY_ALL,
                on_click=export,
                width=page.window.width-5,
                style=fl.ButtonStyle(shape=fl.RoundedRectangleBorder(radius=1)),
            ),
            fl.ElevatedButton(
                "Row Data",
                icon=fl.icons.COPY_ALL,
                on_click=row_export,
                width=page.window.width-5,
                style=fl.ButtonStyle(shape=fl.RoundedRectangleBorder(radius=1)),
            ),
            fl.ElevatedButton(
                "Summary",
                icon=fl.icons.COPY_ALL,
                on_click=sum_export,
                width=page.window.width-5,
                style=fl.ButtonStyle(shape=fl.RoundedRectangleBorder(radius=1)),
            ),
            fl.Card(content=info,width=page.window.width,shape=fl.RoundedRectangleBorder(radius=1))
        ])
    )
fl.app(target=main)